package com.dzb.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;


public class JdbcHelper {
	
	private static final String DATABASE="jdbc.connection";
	private static final String PREFIX="jdbc.connection.";
	private static final String DRIVER_SUFFIX=".driver";
	private static final String URL_SUFFIX=".url";
	private static final String USER_SUFFIX=".user";
	private static final String PASSWORD_SUFFIX=".password";
	private static final String AUTOCOMMIT_SUFFIX=".autocommit";
	private static final String ISOLATION_SUFFIX=".isolation";
	
	private String database;
	private String driver;
	private String url;
	private String username;
	private String password;
	private boolean autocommit;
	private int isolation;  //0,1,2,4,8表示未设置隔离级别，读未提交，读已提交，可重复读，可串行化
	
	private boolean unload=false; //false表示未加载驱动，true表示已经加载
	private Connection connection; //表示存储已经创建好的缓存数据库对象
	private ClassLoader loader=Thread.currentThread().getContextClassLoader();
	
	
   public JdbcHelper() {
	   super();
	   this.init("config.properties");
   }
   
   public JdbcHelper(String path) {
	   super();
	   this.init(path);
   }
   
   private void init(String path) {
	   if(path==null) {
		   System.out.println("未找到系统配置文件");
		   return;
	   }
	   
	   InputStream in=loader.getResourceAsStream(path);
	   Properties props=new Properties();
	   try {
		      props.load(in);
	   } catch (IOException e) {
		      e.printStackTrace();
	   }
	   
	   database=props.getProperty(DATABASE);
	   
	   driver=props.getProperty(PREFIX+database+DRIVER_SUFFIX);
	   
	   url=props.getProperty(PREFIX+database+URL_SUFFIX);
	   
	   username=props.getProperty(PREFIX+database+USER_SUFFIX);
	   
	   password=props.getProperty(PREFIX+database+PASSWORD_SUFFIX);
	   
	   autocommit=Boolean.parseBoolean(props.getProperty(PREFIX+database+AUTOCOMMIT_SUFFIX));
	   
	   isolation=Integer.parseInt(props.getProperty(PREFIX+database+ISOLATION_SUFFIX));
   }
   
   //1.加载驱动
   private void load() {
	   if(unload==false) {
		   try {
			      //Class.forName(driver);
			   Class.forName( driver , true , Thread.currentThread().getContextClassLoader() );
			      unload=true;
		} catch (ClassNotFoundException e) {
			      e.printStackTrace();
		}
	   }
   }
   
   //2.连接数据库
   private void connect() {
	   //判断连接数据库是否有效
	   if(this.invalid()) {
		   //重新创建一个新的连接
		   try {
			connection=DriverManager.getConnection(url, username, password);
			 
			//设置事务的自动提交状态和隔离级别
			this.SetTransisolation();
		} catch (SQLException cause) {
			throw new RuntimeException("数据库连接失败!", cause);  //异常转译
		}
	   }
   }
   

//判断数据库连接是否有效
   private boolean invalid() {
	   try {
		     return connection==null||connection.isValid(3)==false;
	} catch (SQLException cause) {
		     throw new RuntimeException("数据库连接失效",cause);
	}
   }
   
   private void SetTransisolation() {
	  try {
		    connection.setAutoCommit(autocommit);
	     } catch (SQLException e) {
		    e.printStackTrace();
	}
		
	  if(isolation==0||isolation==1||isolation==2||isolation==4||isolation==8) {
		    try {
				connection.setTransactionIsolation(isolation);
			} catch (SQLException e) {
				e.printStackTrace();
			}
	  }
	}
   
   //3.创建一个PreparedStatement对象
   private PreparedStatement prepare(String SQL,boolean generated) {
	   this.load();
	   this.connect();
	   
	   int autogenerated=generated?Statement.RETURN_GENERATED_KEYS:Statement.NO_GENERATED_KEYS;
	   
	   try {
		      return connection.prepareStatement(SQL,autogenerated);
	      } catch (SQLException cause) {
	           throw new RuntimeException("创建PreparedStatement对象失败", cause);
	  }
   }
   
   //4.执行SQL语句，返回执行结果
   public int Update(String SQL,Object...params) {
	   PreparedStatement ps=prepare(SQL,false);
	   setParamter(ps,params);
	   
	   try {
		return ps.executeUpdate();
	} catch (SQLException cause) {
		 throw new RuntimeException("SQL语句执行失败", cause);
	}finally {
		this.close(ps);
	}
   }
 
   //寻找数据库自动增长的键，并且返回其键值
   public int Insert(String SQL,Object...params) {
	   if(params==null||params.length==0) {
			System.out.println("你传入的值是空的");
		}
	   PreparedStatement ps=prepare(SQL,true);
	   setParamter(ps,params);
	   
	   try {
		      ps.executeUpdate();
	        } catch (SQLException cause) {
	        	this.close(ps);
		      throw new RuntimeException("SQL语句执行失败", cause);
	      }
	   
	   int id=-1;
	   ResultSet res=null;
	   try {
		res=ps.getGeneratedKeys();
		if(res.next()) {
			id=res.getInt(1);
		}
	    } catch (SQLException cause) {
		throw new RuntimeException("寻找数据库自动增长键时发生错误", cause);
		
	   }finally {
		   this.close(res);
		   this.close(ps);
	}
	   
	   return id;
   }
   //设置参数化SQL值
   private void setParamter(PreparedStatement ps,Object...params) {
	   if(params!=null&&params.length>0) {
		   try {
			   
		   for(int i=0;i<params.length;i++) {
			   ps.setObject(i+1, params[i]);
		   }
		   
		   }catch (Exception cause) {
			throw new RuntimeException("设置参数化SQL发生错误", cause);
		}
	   }
   }
   
   //5.处理结果集
       public List<Map<String, Object>>Query(String SQL,Object...params){
	   PreparedStatement ps=prepare(SQL, false);
	   setParamter(ps,params);
	   
       ResultSet res=null;
	   try {
	       res=ps.executeQuery();
	} catch (SQLException cause) {
		throw new RuntimeException("SQL语句执行失败", cause);
	}
	   
	List<Map<String, Object>>list=convert(res);
	   
	   this.close(res); 
	   this.close(ps);
	   return list;
   }
   
   private List<Map<String, Object>> convert(ResultSet res) {
	   if(res==null) {
		   System.out.println("该结果集为空");
		   return null;
	   }
	   
	   List<Map<String, Object>>list=new ArrayList<>();
	   try {
		   ResultSetMetaData resultSetMD=res.getMetaData();
		   while(res.next()) {
		       Map<String, Object>map=new HashMap<>();
		       for(int i=1;i<=resultSetMD.getColumnCount();i++) {
		    	   Object value=res.getObject(i);
		    	   String key=resultSetMD.getColumnLabel(i);
		    	   map.put(key, value);
		       }
		       
		       list.add(map);
		   }
	    } catch (SQLException e) {
		       e.printStackTrace();
	}
	return list;
}

   @Deprecated
   public void show(String SQL) {
	   PreparedStatement ps=prepare(SQL, false);
	   ResultSet res=null;
	   try {
		      res=ps.executeQuery();
	} catch (SQLException cause) {
		      throw new RuntimeException("SQL语句执行失败", cause);
	}
	   
	   try {
	   ResultSetMetaData resultSetMD=res.getMetaData();
	   for(int i=1;i<=resultSetMD.getColumnCount();i++) {
		   System.out.print(resultSetMD.getColumnLabel(i)+"\t");
	   }
	   System.out.println();
	   while(res.next()) {
		   for(int j=1;j<=resultSetMD.getColumnCount();j++) {
			   System.out.print(res.getObject(j)+"\t");
		   }
		   System.out.println();
	   }
	   
	   }catch (Exception e) {
		e.printStackTrace();
	}
   }
   
   //6.释放资源
   public void close(AutoCloseable ac) {
	   if(ac!=null) {
		   try {
			      ac.close();
		} catch (Exception e) {
			      e.printStackTrace();
		}
	   }
   }
   
   //销毁当前对象所占用的资源
   public void destory() {
	   this.close(connection);
   }
}
